/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/07/31
 */
package vn.smartware.co.server.dao.registration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.dto.MstFoodDTO;
import vn.smartware.co.dto.MstPriceDTO;
import vn.smartware.co.forms.RegisterFoodForm;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.java.net.commons.Utils;
import vn.smartware.co.server.sql.factory.AbstractRegisterFoodSQLManager;

/**
 * @author thuctt
 * RegisterFoodSQLManager
 */
public class RegisterFoodSQLManager extends AbstractRegisterFoodSQLManager {

	private static Log log = LogFactory.getLog(RegisterFoodSQLManager.class);
	
	/**
	 * makeGetUnitListSQL
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetUnitListSQL(LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" UNIT_CD, ")
		.append(" NAME, ")
		.append(" DESCRIPTION ")
		
		.append(" FROM MSTUNIT ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		;
		
		buf.append(" ORDER BY UNIT_CD ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * get food list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetFoodListSQL(RegisterFoodForm form, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" FOOD.FOOD_NO, ")
		.append(" FOOD.NAME, ")
		.append(" FOOD.DESCRIPTION, ")
		.append(" FOOD.UNIT_CD, ")
		.append(" UNIT.NAME AS UNIT_NAME, ")
		.append(" PRICE.PRICE_NO, ")
		.append(" PRICE.PRICE, ")
		.append(" PRICE.MONEY_TYPE, ")
		.append(" CLS.NAME AS MONEY_TYPE_NAME ")
		
		.append(" FROM MSTFOOD FOOD ")
		
		.append(" LEFT OUTER JOIN MSTFOODPRICE PRICE ")
		.append(" ON FOOD.FOOD_NO = PRICE.FOOD_NO AND PRICE.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS ")
		.append(" ON CLS.KEY1 = 'MONEY_TYPE' AND CLS.VALUE = PRICE.MONEY_TYPE ")
		
		.append(" LEFT OUTER JOIN MSTUNIT UNIT ")
		.append(" ON UNIT.UNIT_CD = FOOD.UNIT_CD AND UNIT.DELETE_DATE IS NULL ")
		
		.append(" WHERE FOOD.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getNoStart())){
			buf.append(" AND FOOD.FOOD_NO >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getNoEnd())){
			buf.append(" AND FOOD.FOOD_NO <= ? ");
		}
		
		buf.append(" ORDER BY FOOD.NAME, PRICE.MONEY_TYPE ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (!Utils.isNullEmpty(form.getNoStart())){
				ps.setInt(i++, Integer.parseInt(form.getNoStart()));
				log.info("FOOD_NO >= " + form.getNoStart());
			}
			
			if (!Utils.isNullEmpty(form.getNoEnd())){
				ps.setInt(i++, Integer.parseInt(form.getNoEnd()));
				log.info("FOOD_NO <= " + form.getNoEnd());
			}
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeleteFoodSQL
	 * @param foodNo
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeDeleteFoodSQL(String foodNo, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTFOOD SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE FOOD_NO = ? AND DELETE_DATE IS NULL ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, foodNo);
			log.info("FOOD_NO = " + foodNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterFoodSQL
	 * @param food
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeRegisterFoodSQL(MstFoodDTO food, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MSTFOOD ( ")
		.append(" NAME, DESCRIPTION, UNIT_CD, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, food.getName());
			log.info("NAME = " + food.getName());
			
			ps.setString(i++, food.getDescription());
			log.info("DESCRIPTION = " + food.getDescription());
			
			ps.setString(i++, food.getUnitCd());
			log.info("UNIT_CD = " + food.getUnitCd());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeUpdateFoodSQL
	 * @param food
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeUpdateFoodSQL(MstFoodDTO food, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTFOOD SET ")
		.append(" NAME = ?, DESCRIPTION = ?, UNIT_CD = ?, ")
		.append(" UPDATE_USER_ID = ?, UPDATE_DATE = SYSDATE() ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		.append(" AND FOOD_NO = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, food.getName());
			log.info("NAME = " + food.getName());
			
			ps.setString(i++, food.getDescription());
			log.info("DESCRIPTION = " + food.getDescription());
			
			ps.setString(i++, food.getUnitCd());
			log.info("UNIT_CD = " + food.getUnitCd());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setString(i++, food.getFoodNo());
			log.info("FOOD_NO = " + food.getFoodNo());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * getMaxFoodNoSQL
	 * @param conn
	 * @throws ServerException 
	 */
	public void getMaxFoodNoSQL(Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(FOOD_NO) AS FOOD_NO ")
		.append(" FROM MSTFOOD ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterPriceSQL
	 * @param foodNo
	 * @param price
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeRegisterPriceSQL(String foodNo, MstPriceDTO price, LoginDTO user,
			Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MSTFOODPRICE ( ")
		.append(" FOOD_NO, MONEY_TYPE, PRICE, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, foodNo);
			log.info("FOOD_NO = " + foodNo);
			
			ps.setString(i++, price.getMoneyType());
			log.info("MONEY_TYPE = " + price.getMoneyType());
			
			ps.setString(i++, price.getPrice());
			log.info("PRICE = " + price.getPrice());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeletePriceListSQL
	 * @param foodNo
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeDeletePriceListSQL(String foodNo, LoginDTO user,
			Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTFOODPRICE SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE FOOD_NO = ? AND DELETE_DATE IS NULL ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, foodNo);
			log.info("FOOD_NO = " + foodNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
}
